{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# KPI Validation\n",
    "\n",
    "### Download the dataset from [Kaggle](https://www.kaggle.com/carrie1/ecommerce-data) and update path accordingly."
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Imports"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from typing import List, Tuple, Union\n",
    "\n",
    "from IPython.core.display import display"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Load/Preprocess Ecommerce data"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "source": [
    "ecom_df = pd.read_csv('./data/ecom_data.csv')\n",
    "\n",
    "# A few rows have negative values for UnitPrice and Quantity. Consider only non-negative UnitPrice & Quantity\n",
    "ecom_df = ecom_df[ecom_df['UnitPrice'] >= 0]\n",
    "ecom_df = ecom_df[ecom_df['Quantity'] >= 0]\n",
    "\n",
    "# Turn CustomerID into an object, instead of numerical\n",
    "ecom_df['CustomerID'] = ecom_df['CustomerID'].astype(str)\n",
    "\n",
    "# Convert InvoiceDate to a datetime object\n",
    "ecom_df['date'] = pd.to_datetime(ecom_df['InvoiceDate'])\n",
    "# ecom_df['InvoiceDate'] = pd.to_datetime(ecom_df['InvoiceDate'])\n",
    "# ecom_df.rename(columns={'InvoiceDate': 'date'}, inplace=True)\n",
    "\n",
    "# Feature extraction for PeriodOfDay\n",
    "def get_day_period(hour: int) -> str:\n",
    "    if hour < 6:\n",
    "        return \"night\"\n",
    "    elif 6 <= hour < 12:\n",
    "        return \"morning\"\n",
    "    elif 12 <= hour < 17:\n",
    "        return \"afternoon\"\n",
    "    elif 17 <= hour < 22:\n",
    "        return \"evening\"\n",
    "    else:\n",
    "        return \"night\"\n",
    "ecom_df['PeriodOfDay'] = ecom_df['date'].apply(lambda d: get_day_period(d.hour))\n",
    "\n",
    "# Feature extraction for DayOfWeek\n",
    "ecom_df['DayOfWeek'] = ecom_df['date'].apply(lambda d: d.strftime('%A'))\n",
    "\n",
    "# Drop Description, not needed. Keep StockCode(useful for differentiating the products)\n",
    "ecom_df.drop('Description', axis=1, inplace=True)\n",
    "\n",
    "# FeatureExtraction for ItemTotalPrice\n",
    "ecom_df['ItemTotalPrice'] = ecom_df['UnitPrice'] * ecom_df['Quantity']"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### For testing purposes, I'll create some date columns in different forms"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "# format: %m%Y%d %X\n",
    "ecom_df['date2'] = ecom_df['date'].apply(lambda d: d.strftime('%m%Y%d %X'))\n",
    "\n",
    "# Unix time in seconds\n",
    "ecom_df['unix_secs'] = ecom_df['date'].apply(lambda d: d.timestamp()).astype(int)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "source": [
    "ecom_df"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "       InvoiceNo StockCode  Quantity      InvoiceDate  UnitPrice CustomerID  \\\n",
       "0         536365    85123A         6   12/1/2010 8:26       2.55    17850.0   \n",
       "1         536365     71053         6   12/1/2010 8:26       3.39    17850.0   \n",
       "2         536365    84406B         8   12/1/2010 8:26       2.75    17850.0   \n",
       "3         536365    84029G         6   12/1/2010 8:26       3.39    17850.0   \n",
       "4         536365    84029E         6   12/1/2010 8:26       3.39    17850.0   \n",
       "...          ...       ...       ...              ...        ...        ...   \n",
       "541904    581587     22613        12  12/9/2011 12:50       0.85    12680.0   \n",
       "541905    581587     22899         6  12/9/2011 12:50       2.10    12680.0   \n",
       "541906    581587     23254         4  12/9/2011 12:50       4.15    12680.0   \n",
       "541907    581587     23255         4  12/9/2011 12:50       4.15    12680.0   \n",
       "541908    581587     22138         3  12/9/2011 12:50       4.95    12680.0   \n",
       "\n",
       "               Country                date PeriodOfDay  DayOfWeek  \\\n",
       "0       United Kingdom 2010-12-01 08:26:00     morning  Wednesday   \n",
       "1       United Kingdom 2010-12-01 08:26:00     morning  Wednesday   \n",
       "2       United Kingdom 2010-12-01 08:26:00     morning  Wednesday   \n",
       "3       United Kingdom 2010-12-01 08:26:00     morning  Wednesday   \n",
       "4       United Kingdom 2010-12-01 08:26:00     morning  Wednesday   \n",
       "...                ...                 ...         ...        ...   \n",
       "541904          France 2011-12-09 12:50:00   afternoon     Friday   \n",
       "541905          France 2011-12-09 12:50:00   afternoon     Friday   \n",
       "541906          France 2011-12-09 12:50:00   afternoon     Friday   \n",
       "541907          France 2011-12-09 12:50:00   afternoon     Friday   \n",
       "541908          France 2011-12-09 12:50:00   afternoon     Friday   \n",
       "\n",
       "        ItemTotalPrice              date2   unix_secs  \n",
       "0                15.30  12201001 08:26:00  1291191960  \n",
       "1                20.34  12201001 08:26:00  1291191960  \n",
       "2                22.00  12201001 08:26:00  1291191960  \n",
       "3                20.34  12201001 08:26:00  1291191960  \n",
       "4                20.34  12201001 08:26:00  1291191960  \n",
       "...                ...                ...         ...  \n",
       "541904           10.20  12201109 12:50:00  1323435000  \n",
       "541905           12.60  12201109 12:50:00  1323435000  \n",
       "541906           16.60  12201109 12:50:00  1323435000  \n",
       "541907           16.60  12201109 12:50:00  1323435000  \n",
       "541908           14.85  12201109 12:50:00  1323435000  \n",
       "\n",
       "[531283 rows x 13 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>InvoiceNo</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>UnitPrice</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>Country</th>\n",
       "      <th>date</th>\n",
       "      <th>PeriodOfDay</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>ItemTotalPrice</th>\n",
       "      <th>date2</th>\n",
       "      <th>unix_secs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>536365</td>\n",
       "      <td>85123A</td>\n",
       "      <td>6</td>\n",
       "      <td>12/1/2010 8:26</td>\n",
       "      <td>2.55</td>\n",
       "      <td>17850.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>morning</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>15.30</td>\n",
       "      <td>12201001 08:26:00</td>\n",
       "      <td>1291191960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>536365</td>\n",
       "      <td>71053</td>\n",
       "      <td>6</td>\n",
       "      <td>12/1/2010 8:26</td>\n",
       "      <td>3.39</td>\n",
       "      <td>17850.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>morning</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>20.34</td>\n",
       "      <td>12201001 08:26:00</td>\n",
       "      <td>1291191960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>536365</td>\n",
       "      <td>84406B</td>\n",
       "      <td>8</td>\n",
       "      <td>12/1/2010 8:26</td>\n",
       "      <td>2.75</td>\n",
       "      <td>17850.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>morning</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>22.00</td>\n",
       "      <td>12201001 08:26:00</td>\n",
       "      <td>1291191960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>536365</td>\n",
       "      <td>84029G</td>\n",
       "      <td>6</td>\n",
       "      <td>12/1/2010 8:26</td>\n",
       "      <td>3.39</td>\n",
       "      <td>17850.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>morning</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>20.34</td>\n",
       "      <td>12201001 08:26:00</td>\n",
       "      <td>1291191960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>536365</td>\n",
       "      <td>84029E</td>\n",
       "      <td>6</td>\n",
       "      <td>12/1/2010 8:26</td>\n",
       "      <td>3.39</td>\n",
       "      <td>17850.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>morning</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>20.34</td>\n",
       "      <td>12201001 08:26:00</td>\n",
       "      <td>1291191960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541904</th>\n",
       "      <td>581587</td>\n",
       "      <td>22613</td>\n",
       "      <td>12</td>\n",
       "      <td>12/9/2011 12:50</td>\n",
       "      <td>0.85</td>\n",
       "      <td>12680.0</td>\n",
       "      <td>France</td>\n",
       "      <td>2011-12-09 12:50:00</td>\n",
       "      <td>afternoon</td>\n",
       "      <td>Friday</td>\n",
       "      <td>10.20</td>\n",
       "      <td>12201109 12:50:00</td>\n",
       "      <td>1323435000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541905</th>\n",
       "      <td>581587</td>\n",
       "      <td>22899</td>\n",
       "      <td>6</td>\n",
       "      <td>12/9/2011 12:50</td>\n",
       "      <td>2.10</td>\n",
       "      <td>12680.0</td>\n",
       "      <td>France</td>\n",
       "      <td>2011-12-09 12:50:00</td>\n",
       "      <td>afternoon</td>\n",
       "      <td>Friday</td>\n",
       "      <td>12.60</td>\n",
       "      <td>12201109 12:50:00</td>\n",
       "      <td>1323435000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541906</th>\n",
       "      <td>581587</td>\n",
       "      <td>23254</td>\n",
       "      <td>4</td>\n",
       "      <td>12/9/2011 12:50</td>\n",
       "      <td>4.15</td>\n",
       "      <td>12680.0</td>\n",
       "      <td>France</td>\n",
       "      <td>2011-12-09 12:50:00</td>\n",
       "      <td>afternoon</td>\n",
       "      <td>Friday</td>\n",
       "      <td>16.60</td>\n",
       "      <td>12201109 12:50:00</td>\n",
       "      <td>1323435000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541907</th>\n",
       "      <td>581587</td>\n",
       "      <td>23255</td>\n",
       "      <td>4</td>\n",
       "      <td>12/9/2011 12:50</td>\n",
       "      <td>4.15</td>\n",
       "      <td>12680.0</td>\n",
       "      <td>France</td>\n",
       "      <td>2011-12-09 12:50:00</td>\n",
       "      <td>afternoon</td>\n",
       "      <td>Friday</td>\n",
       "      <td>16.60</td>\n",
       "      <td>12201109 12:50:00</td>\n",
       "      <td>1323435000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541908</th>\n",
       "      <td>581587</td>\n",
       "      <td>22138</td>\n",
       "      <td>3</td>\n",
       "      <td>12/9/2011 12:50</td>\n",
       "      <td>4.95</td>\n",
       "      <td>12680.0</td>\n",
       "      <td>France</td>\n",
       "      <td>2011-12-09 12:50:00</td>\n",
       "      <td>afternoon</td>\n",
       "      <td>Friday</td>\n",
       "      <td>14.85</td>\n",
       "      <td>12201109 12:50:00</td>\n",
       "      <td>1323435000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>531283 rows × 13 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 4
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## KPI Validation functions"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Validate that KPI & Datetime columns exists\n",
    "**Preliminary Check: If this doesn't pass, other Validation checks will fail for this same reason.**"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "def column_exists(\n",
    "    df: pd.core.frame.DataFrame,\n",
    "    column_name: Union[str, List[str]]\n",
    ") -> Tuple[bool, str]:\n",
    "    \"\"\"Validates if a column, or list of columns exists in the input DataFrame\n",
    "\n",
    "    :param df: A pandas DataFrame\n",
    "    :type df: pd.core.frame.DataFrame\n",
    "    :param column_name: Name of the column to check existence. Or a list of column names to check existence of.\n",
    "    :type column_name: Union[str, List[str]]\n",
    "    :return: returns a tuple with the status as a bool and a status message\n",
    "    :rtype: Tuple[bool, str]\n",
    "    \"\"\"\n",
    "    # Lambda function templates to create string output\n",
    "    valid_str = 'Accepted!'\n",
    "    single_col_str = lambda col: f'\"{col}\" was not found as a column in the table!'\n",
    "    multi_col_str = lambda cols: f'''{\", \".join([f'\"{col}\"' for col in cols])} were not found as columns in the table!'''\n",
    "\n",
    "    # column_name is a string. Single column to check\n",
    "    if isinstance(column_name, str):\n",
    "        status = column_name in df.columns\n",
    "        message = valid_str if status else single_col_str(column_name)\n",
    "        return status, message\n",
    "    # column_name is a list. N cols to check\n",
    "    else:\n",
    "        # Find which cols do not exist in DataFrame\n",
    "        not_found_cols = [col for col in column_name if col not in df.columns]\n",
    "        \n",
    "        if len(not_found_cols) == 1:\n",
    "            return False, single_col_str(not_found_cols[0])\n",
    "        if len(not_found_cols) > 1:\n",
    "            return False, multi_col_str(not_found_cols)\n",
    "        else:\n",
    "            return True, valid_str"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Validate that aggregation type is valid for the column type (Categorical column won't work with mean/sum aggregation)\n",
    "**Check #1 in documentation**"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "def validate_agg_type_fits_column(\n",
    "    df: pd.core.frame.DataFrame, \n",
    "    column_name: str, \n",
    "    agg_type: str \n",
    ") -> Tuple[bool, str]:\n",
    "    \"\"\"Validates if agggregation type is supported and is valid for the specified column.\n",
    "\n",
    "    :param df: A pandas DataFrame\n",
    "    :type df: pd.core.frame.DataFrame\n",
    "    :param column_name: Name of the column to validate\n",
    "    :type column_name: str\n",
    "    :param agg_type: A supported aggregation function\n",
    "    :type agg_type: str\n",
    "    :return: returns a tuple with the status as a bool and a status message\n",
    "    :rtype: bool\n",
    "    \"\"\"\n",
    "    # Check if aggregation type is supported\n",
    "    SUPPORTED_AGGREGATIONS = [\"mean\", \"sum\", \"count\"]\n",
    "    if agg_type not in SUPPORTED_AGGREGATIONS:\n",
    "        return False, f'\"{agg_type}\" aggregation is not supported.\\nSupported aggregations are {\", \".join(SUPPORTED_AGGREGATIONS)}'\n",
    "\n",
    "    # Check if trying to use a numerical aggregation on a categorical column.\n",
    "    if str(df[column_name].dtype) == 'object' and agg_type != 'count':\n",
    "        return False, f'\"{column_name}\" column is categorical. Quantitative data is required to perform {agg_type} aggregation.'\n",
    "\n",
    "    return True, 'Accepted!'"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Validate that the KPI is not the date column.\n",
    "**Check #2 in documentation**"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "source": [
    "def validate_kpi_not_datetime(\n",
    "    df: pd.core.frame.DataFrame,\n",
    "    kpi_column_name: str,\n",
    "    date_column_name: str\n",
    ") -> Tuple[bool, str]:\n",
    "    \"\"\"Validates if kpi column is not the same as the date column.\n",
    "\n",
    "    :param df: A pandas DataFrame\n",
    "    :type df: pd.core.frame.DataFrame\n",
    "    :param kpi_column_name: Name of the column used for KPI\n",
    "    :type kpi_column_name: str\n",
    "    :param date_column_name: Name of the date column\n",
    "    :type date_column_name: str\n",
    "    :return: returns a tuple with the status as a bool and a status message\n",
    "    :rtype: Tuple[bool, str]\n",
    "    \"\"\"\n",
    "    status = kpi_column_name != date_column_name\n",
    "    message = 'Accepted!' if status else 'KPI column cannot be the same as the date column'\n",
    "    return status, message"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Validate that the date column is parseable\n",
    "**Check #3 in documentation**"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "source": [
    "from pandas.api.types import is_datetime64_any_dtype as is_datetime\n",
    "\n",
    "def validate_date_column_is_parseable(\n",
    "    df: pd.core.frame.DataFrame,\n",
    "    date_column_name: str,\n",
    "    date_format: str = None,\n",
    "    unix_unit: str = None,\n",
    ") -> Tuple[bool, str]:\n",
    "    \"\"\"Validates if specified date column is parseable.\n",
    "\n",
    "    :param df: A pandas DataFrame\n",
    "    :type df: pd.core.frame.DataFrame\n",
    "    :param date_column_name: Name of the date column\n",
    "    :type date_column_name: str\n",
    "    :param date_format: Specified strftime to parse (https://docs.python.org/3/library/datetime.html#strftime-and-strptime-behavior), defaults to None\n",
    "    :type date_format: str, optional\n",
    "    :param unix_unit: The time unit if specified date column is in Unix format, defaults to None\n",
    "    :type unix_unit: str, optional\n",
    "    :return: returns a tuple with the status as a bool and a status message\n",
    "    :rtype: Tuple[bool, str]\n",
    "    \"\"\"\n",
    "    valid_str = 'Accepted!'\n",
    "    # Exit early if date column is a datetime object.\n",
    "    if is_datetime(df[date_column_name]):\n",
    "        return True, valid_str\n",
    "    \n",
    "    generic_err_msg = f'Unable to parse \"{date_column_name}\" column. Check that your date column is formatted properly and consistely.'\n",
    "    out_of_bounds_msg = f'Timestamps in \"{date_column_name}\" were out of bounds. Check that your date column is formatted properly and consistely.'\n",
    "\n",
    "    if unix_unit:\n",
    "        # If a unix_unit is specified, it will try to convert with this unit\n",
    "        try:\n",
    "            pd.to_datetime(df[date_column_name], unit=unix_unit, infer_datetime_format=True)\n",
    "        except pd.errors.OutOfBoundsDatetime as ex:\n",
    "            return False, out_of_bounds_msg\n",
    "        except Exception as ex:\n",
    "            return False, f\"{generic_err_msg}\"\n",
    "    elif date_format:\n",
    "        # If a date_format is specified, it will try to convert with this format\n",
    "        try:\n",
    "            pd.to_datetime(df[date_column_name], format=date_format, infer_datetime_format=True)\n",
    "        except pd.errors.OutOfBoundsDatetime as ex:\n",
    "            return False, out_of_bounds_msg\n",
    "        except Exception as ex:\n",
    "            return False, f\"{generic_err_msg}\"\n",
    "    else:\n",
    "        # If neither date_format or unix_unit, let pandas do its best to infer datetime format.\n",
    "        try:\n",
    "            pd.to_datetime(df[date_column_name], infer_datetime_format=True)\n",
    "        except pd.errors.OutOfBoundsDatetime as ex:\n",
    "            return False, out_of_bounds_msg\n",
    "        except Exception as ex:\n",
    "            return False, f\"{generic_err_msg}\"\n",
    "    \n",
    "    # datetime column is parseable if code reaches here.\n",
    "    return True, valid_str"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Wrapper function"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "source": [
    "def validate_kpi(\n",
    "    df: pd.core.frame.DataFrame,\n",
    "    kpi_column_name: str,\n",
    "    agg_type: str,\n",
    "    date_column_name: str,\n",
    "    date_format: str = None,\n",
    "    unix_unit: str = None,\n",
    "    debug: bool = False,\n",
    ") -> Tuple[bool, str]:\n",
    "    \"\"\"A wrapper function which cleanly invokes each validation check and breaks if it hits a falsy check.\n",
    "\n",
    "    :param df: A pandas DataFrame\n",
    "    :type df: pd.core.frame.DataFrame\n",
    "    :param kpi_column_name: Name of the column used for KPI\n",
    "    :type kpi_column_name: str\n",
    "    :param agg_type: A supported aggregation function\n",
    "    :type agg_type: str\n",
    "    :param date_column_name: Name of the date column\n",
    "    :type date_column_name: str\n",
    "    :param date_format: Specified strftime to parse (https://docs.python.org/3/library/datetime.html#strftime-and-strptime-behavior), defaults to None\n",
    "    :type date_format: str, optional\n",
    "    :param unix_unit: The time unit if specified date column is in Unix format, defaults to None\n",
    "    :type unix_unit: str, optional\n",
    "    :param debug: Bool for using debug mode with extra print statements at each validation, defaults to False\n",
    "    :type debug: bool, optional\n",
    "    :return: returns a tuple with the status as a bool and a status message\n",
    "    :rtype: Tuple[bool, str]\n",
    "    \"\"\"\n",
    "\n",
    "    # Preliminary Check that the KPI column exists\n",
    "    # This check must be done independently. Otherwise, the other 3 checks will fail!\n",
    "    status_bool, status_msg = column_exists(df, column_name=[kpi_column_name, date_column_name])\n",
    "    if debug:\n",
    "        print('Check #0: KPI column and Datetime column exist in DataFrame')\n",
    "        print(status_bool, status_msg, sep=\", \")\n",
    "    if not status_bool:\n",
    "        return status_bool, status_msg\n",
    "    \n",
    "    # Validation check results\n",
    "    validations = [\n",
    "        {'debug_str': 'Check #1: Validate column fits agg type', 'status': validate_agg_type_fits_column(df, column_name=kpi_column_name, agg_type=agg_type)}, \n",
    "        {'debug_str': 'Check #2: Validate kpi not datetime', 'status': validate_kpi_not_datetime(df, kpi_column_name=kpi_column_name, date_column_name=date_column_name)}, \n",
    "        {'debug_str': 'Check #3: Validate date column is parseable', 'status': validate_date_column_is_parseable(df, date_column_name=date_column_name, date_format=date_format, unix_unit=unix_unit)},\n",
    "    ]\n",
    "    for validation in validations:\n",
    "        status_bool, status_msg = validation['status']\n",
    "        if debug:\n",
    "            print(validation['debug_str'])\n",
    "            print(status_bool, status_msg, sep=\", \")\n",
    "        if not status_bool:\n",
    "            return status_bool, status_msg\n",
    "\n",
    "    # All Validation Checks have passed if code reaches here!\n",
    "    return True, 'Accepted!'"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Test Cases"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "source": [
    "# Passes all checks\n",
    "validate_kpi(ecom_df.tail(100), kpi_column_name='ItemTotalPrice', agg_type='sum', date_column_name='date')"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(True, 'Accepted!')"
      ]
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "source": [
    "# Passes all checks\n",
    "# Example with debug mode\n",
    "validate_kpi(ecom_df.tail(100), kpi_column_name='ItemTotalPrice', agg_type='sum', date_column_name='date', debug=True)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Check #0: KPI column and Datetime column exist in DataFrame\n",
      "True, Accepted!\n",
      "Check #1: Validate column fits agg type\n",
      "True, Accepted!\n",
      "Check #2: Validate kpi not datetime\n",
      "True, Accepted!\n",
      "Check #3: Validate date column is parseable\n",
      "True, Accepted!\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(True, 'Accepted!')"
      ]
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "source": [
    "# Column does not exist\n",
    "validate_kpi(ecom_df.tail(100), kpi_column_name='Price', agg_type='mean', date_column_name='date')"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(False, '\"Price\" was not found as a column in the table!')"
      ]
     },
     "metadata": {},
     "execution_count": 12
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "source": [
    "# Multiple columns do not exist\n",
    "validate_kpi(ecom_df.tail(100), kpi_column_name='Price', agg_type='mean', date_column_name='day')"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(False, '\"Price\", \"day\" were not found as columns in the table!')"
      ]
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "source": [
    "# Should not be able to aggregate PeriodOfDay (categorical) with mean aggregation.\n",
    "validate_kpi(ecom_df.tail(100), kpi_column_name='PeriodOfDay', agg_type='mean', date_column_name='date')"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(False,\n",
       " '\"PeriodOfDay\" column is categorical. Quantitative data is required to perform mean aggregation.')"
      ]
     },
     "metadata": {},
     "execution_count": 14
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "source": [
    "# kpi_column_name and date_column_name are the same. Should be Falsy\n",
    "validate_kpi(ecom_df.tail(100), kpi_column_name='date', agg_type='mean', date_column_name='date')"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(False, 'KPI column cannot be the same as the date column')"
      ]
     },
     "metadata": {},
     "execution_count": 15
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "source": [
    "# kpi_column_name and date_column_name are the same. Should be Falsy\n",
    "# Same as previous, but test with debug mode\n",
    "validate_kpi(ecom_df.tail(100), kpi_column_name='date', agg_type='mean', date_column_name='date', debug=True)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Check #0: KPI column and Datetime column exist in DataFrame\n",
      "True, Accepted!\n",
      "Check #1: Validate column fits agg type\n",
      "True, Accepted!\n",
      "Check #2: Validate kpi not datetime\n",
      "False, KPI column cannot be the same as the date column\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(False, 'KPI column cannot be the same as the date column')"
      ]
     },
     "metadata": {},
     "execution_count": 16
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "source": [
    "# Try running while inputting a custom date format\n",
    "# date2 had following format (%m%Y%d %X)\n",
    "# This should NOT pass, since I am using a different format.\n",
    "validate_kpi(ecom_df.tail(100), kpi_column_name='ItemTotalPrice', agg_type='mean', date_column_name='date2', date_format=\"%Y%m%d%X\")"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(False,\n",
       " 'Timestamps in \"date2\" were out of bounds. Check that your date column is formatted properly and consistely.')"
      ]
     },
     "metadata": {},
     "execution_count": 17
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "source": [
    "# Try running while inputting a custom date format\n",
    "# date2 had following format (%m%Y%d %X)\n",
    "# This should pass as I'm using the same format\n",
    "validate_kpi(ecom_df.tail(100), kpi_column_name='ItemTotalPrice', agg_type='mean', date_column_name='date2', date_format=\"%m%Y%d %X\")"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(True, 'Accepted!')"
      ]
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "source": [
    "pd.to_datetime(ecom_df['unix_secs'], unit='ns')"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0        1970-01-01 00:00:01.291191960\n",
       "1        1970-01-01 00:00:01.291191960\n",
       "2        1970-01-01 00:00:01.291191960\n",
       "3        1970-01-01 00:00:01.291191960\n",
       "4        1970-01-01 00:00:01.291191960\n",
       "                      ...             \n",
       "541904   1970-01-01 00:00:01.323435000\n",
       "541905   1970-01-01 00:00:01.323435000\n",
       "541906   1970-01-01 00:00:01.323435000\n",
       "541907   1970-01-01 00:00:01.323435000\n",
       "541908   1970-01-01 00:00:01.323435000\n",
       "Name: unix_secs, Length: 531283, dtype: datetime64[ns]"
      ]
     },
     "metadata": {},
     "execution_count": 22
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "source": [
    "validate_kpi(ecom_df.tail(100), kpi_column_name='ItemTotalPrice', agg_type='mean', date_column_name='unix_secs', unix_unit='ns')"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(True, 'Accepted!')"
      ]
     },
     "metadata": {},
     "execution_count": 20
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "source": [
    "from pandasql import sqldf"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "source": [
    "sql = '''\n",
    "select dist_group, count(*)\n",
    "from\n",
    "(\n",
    " select case when UnitPrice between 0 and 10 then '(0, 10)'\n",
    "             when UnitPrice between 10 and 20 then '(10, 20)' end as dist_group\n",
    " from ecom_df\n",
    ")\n",
    "group by dist_group\n",
    "'''\n",
    "sqldf(sql, locals())"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "  dist_group  count(*)\n",
       "0       None      3767\n",
       "1    (0, 10)    507341\n",
       "2   (10, 20)     20175"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dist_group</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>3767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>(0, 10)</td>\n",
       "      <td>507341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>(10, 20)</td>\n",
       "      <td>20175</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 20
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "source": [
    "sql = '''\n",
    "SELECT Country, count() FROM ecom_df GROUP BY Country\n",
    "'''\n",
    "sqldf(sql, locals())"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                 Country  count()\n",
       "0              Australia     1185\n",
       "1                Austria      398\n",
       "2                Bahrain       18\n",
       "3                Belgium     2031\n",
       "4                 Brazil       32\n",
       "5                 Canada      151\n",
       "6        Channel Islands      748\n",
       "7                 Cyprus      614\n",
       "8         Czech Republic       25\n",
       "9                Denmark      380\n",
       "10                  EIRE     7894\n",
       "11    European Community       60\n",
       "12               Finland      685\n",
       "13                France     8408\n",
       "14               Germany     9042\n",
       "15                Greece      145\n",
       "16             Hong Kong      284\n",
       "17               Iceland      182\n",
       "18                Israel      295\n",
       "19                 Italy      758\n",
       "20                 Japan      321\n",
       "21               Lebanon       45\n",
       "22             Lithuania       35\n",
       "23                 Malta      112\n",
       "24           Netherlands     2363\n",
       "25                Norway     1072\n",
       "26                Poland      330\n",
       "27              Portugal     1501\n",
       "28                   RSA       58\n",
       "29          Saudi Arabia        9\n",
       "30             Singapore      222\n",
       "31                 Spain     2485\n",
       "32                Sweden      451\n",
       "33           Switzerland     1967\n",
       "34                   USA      179\n",
       "35  United Arab Emirates       68\n",
       "36        United Kingdom   486284\n",
       "37           Unspecified      446"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>count()</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Australia</td>\n",
       "      <td>1185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austria</td>\n",
       "      <td>398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bahrain</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>2031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Canada</td>\n",
       "      <td>151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Channel Islands</td>\n",
       "      <td>748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Cyprus</td>\n",
       "      <td>614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Czech Republic</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Denmark</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>EIRE</td>\n",
       "      <td>7894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>European Community</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Finland</td>\n",
       "      <td>685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>France</td>\n",
       "      <td>8408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Germany</td>\n",
       "      <td>9042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Greece</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Iceland</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Israel</td>\n",
       "      <td>295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Italy</td>\n",
       "      <td>758</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Japan</td>\n",
       "      <td>321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Lebanon</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Lithuania</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Malta</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>2363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Norway</td>\n",
       "      <td>1072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Poland</td>\n",
       "      <td>330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>1501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>RSA</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Singapore</td>\n",
       "      <td>222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Spain</td>\n",
       "      <td>2485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Sweden</td>\n",
       "      <td>451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>1967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>USA</td>\n",
       "      <td>179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>486284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Unspecified</td>\n",
       "      <td>446</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 21
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  }
 ],
 "metadata": {
  "orig_nbformat": 4,
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}